package com.situ.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.situ.bean.Book;
import com.situ.bean.Card;
import com.situ.bean.Record;


public class LibraryDAO {
    
    public List<Book> getAllBook() throws SQLException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql = "select * from book order by time desc";
        ResultSet rs = JDBCUtils.searchD(sql, null);
        List<Book> bookList = new ArrayList<Book>();
        while (rs.next()) {
            Book book = new Book();
            book.setBookId(rs.getInt(2));
            book.setBookName(rs.getString(3));
            book.setAuthor(rs.getString(4));  
            book.setPress(rs.getString(5));
            if (rs.getString(4).length() <= 0) {
                book.setAuthor("空");                
            }
            if (rs.getString(5).length() <= 0) {
                book.setPress("空");                
            }
            book.setTime(rs.getInt(6));
            book.setStatus(rs.getInt(7));
            bookList.add(book);
        }
        
        JDBCUtils.disconnects();
        return bookList;
    }
    
    public int insertBook(Book book) throws SQLException, MyException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql01 = "select * from book where book_id =" + book.getBookId();
        ResultSet rs = JDBCUtils.searchD(sql01, null);
        while (rs.next()) {
            JDBCUtils.disconnects();
            throw new MyException("该图书ID已经存在");
        }

        String sql = "INSERT INTO book (book_id, book_name, author, press, time, status) VALUES(?, ?, ?, ?, ?, ?);";
        Object[] arr = new Object[6];
        if (book != null) {
            arr[0] = book.getBookId();
            arr[1] = book.getBookName();
            arr[2] = book.getAuthor();
            arr[3] = book.getPress();
            arr[4] = book.getTime();
            arr[5] = book.getStatus();
        }
        int n = JDBCUtils.updateU(sql, arr);

        JDBCUtils.disconnects();
        return n;
    }
    
    public int insertCard(Card card) throws SQLException, MyException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql01 = "select * from card where card_id =" + card.getCardId();
        ResultSet rs = JDBCUtils.searchD(sql01, null);
        if (rs.next()) {
            JDBCUtils.disconnects();
            throw new MyException("该借书卡ID已经存在");
        }

        String sql = "INSERT INTO card (card_id, card_name) VALUES(?, ?);";
        Object[] arr = new Object[2];
        if (card != null) {
            arr[0] = card.getCardId();
            arr[1] = card.getCardName();
        }
        int n = JDBCUtils.updateU(sql, arr);

        JDBCUtils.disconnects();
        return n;
    }
    
    public int insertRecord(int bookId, int cardId) throws SQLException, MyException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql01 = "select status from book where book_id =" + bookId;
        ResultSet rs01 = JDBCUtils.searchD(sql01, null);
        String sql02 = "select * from card where card_id =" + cardId;
        ResultSet rs02 = JDBCUtils.searchD(sql02, null);
        if (!rs01.next()) {
            JDBCUtils.disconnects();
            throw new MyException("该图书不存在");
        }
        if (rs01.next() && (rs01.getInt(1) == 1)) {
            JDBCUtils.disconnects();
            throw new MyException("该图书已被借出"); 
        }
        if (!rs02.next()) {
            JDBCUtils.disconnects();
            throw new MyException("该借书卡不存在");
        }

        String sql = "INSERT INTO record (book_id, card_id, borrow_time) VALUES(?, ?, ?);";
        Object[] arr = new Object[3];
        arr[0] = bookId;
        arr[1] = cardId;
        arr[2] = new Date();
        int n = JDBCUtils.updateU(sql, arr);
        
        if (n > 0) {
            String sql03 = "UPDATE book set status = 1 where book_id =" + bookId;
            JDBCUtils.updateU(sql03, null);
        }

        JDBCUtils.disconnects();
        return n;
    }
    
    public int returnBook(int bookId) throws SQLException, MyException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql01 = "select status from book where book_id =" + bookId;
        ResultSet rs01 = JDBCUtils.searchD(sql01, null);
        if (rs01.next()) {
            Integer num = new Integer(0);
            num.equals(rs01.getInt(1));
            throw new MyException("该图书并未借出");
        }
        
        String sql = "UPDATE record set status = ? where book_id = " + bookId;
        Object[] arr = new Object[1];
        arr[1] = new Date();
        int n = JDBCUtils.updateU(sql, arr);
        
        if (n > 0) {
            String sql03 = "UPDATE book set status = 0 where book_id =" + bookId;
            JDBCUtils.updateU(sql03, null);
        }

        JDBCUtils.disconnects();
        return n;
    }
    
    public ResultSet getCardBords(int cardId) throws SQLException, MyException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql01 = "select * from card where card_id = " + cardId;
        ResultSet rs01 = JDBCUtils.searchD(sql01, null);
        if (!rs01.next()) {
            throw new MyException("该卡号不存在");
        }
        
        String sql = "select r.book_id, b.book_name, r.borrow_time, r.return_time from record as r\r\n" + 
                "join book as b\r\n" + 
                "on r.book_id = b.book_id\r\n" + 
                "where r.card_id = " + cardId;
        ResultSet rs = JDBCUtils.searchD(sql, null);
       
        return rs;
    }
    
    public ResultSet getBookBords(int bookId) throws SQLException, MyException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql01 = "select * from book where book_id = " + bookId;
        ResultSet rs01 = JDBCUtils.searchD(sql01, null);
        if (!rs01.next()) {
            throw new MyException("该图书不存在");
        }
        
        String sql = "select r.card_id, c.card_name, r.borrow_time, r.return_time from record as r\r\n" + 
                "join card as c\r\n" + 
                "on r.card_id = c.card_id\r\n" + 
                "where r.book_id = " + bookId;
        ResultSet rs = JDBCUtils.searchD(sql, null);
       
        return rs;
    }
    

}
